package com.gs.create.table.utils;


import com.google.common.collect.Lists;
import com.gs.common.tool.utils.CollectionUtil;
import com.gs.common.tool.utils.PoJoUtil;
import com.gs.common.tool.utils.StringUtil;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;
import lombok.experimental.FieldNameConstants;

import java.util.List;
import java.util.Objects;
import java.util.concurrent.atomic.AtomicReference;
import java.util.stream.Collectors;

/**
 * sql工具类
 * @author: guos
 * @date: 2025/09/23 16:47
 **/
public class SqlUtil {


    private static final String newLine = "\n";


    public static void main(String[] args) {
        List<String> colStrList = Lists.newArrayList(
                "id|varchar|64|1,0|主键",
                "task_id|varchar|32,''|0|任务id",
                "status|int|11,0|0|任务状态: 1-等待 2-下载中 3-成功 4-失败",
                "message|varchar|32,''|0|结果信息",
                "file_name|varchar|100,''|0|文件名",
                "file_url|varchar|100,''|0|文件地址",
                "file_size|int|11,0|0|文件大小",
                "finish_time|datetime|0|0|完成时间",
                "crt_user|varchar|60,''|0|创建人工号",
                "crt_name|varchar|60,''|0|创建人姓名",
                "crt_time|datetime|0|0|创建时间",
                "upd_time|timestamp|0|0|更新时间");
        //genCreateTableSql("excel_export_download_info", "excel导出下载中心表", colStrList);
//        List<UpdateColumnInfo> columnInfoList = Lists.newArrayList(
//                new UpdateColumnInfo("id", true, "id", true),
//                new UpdateColumnInfo("bind_user_no", false, "upd_user", true),
//                new UpdateColumnInfo("bind_user_name", false, "upd_name", true),
//                new UpdateColumnInfo("bind_time", false, "upd_time", true)
//        );
//        genConcatUpdateSql("my_test", "", columnInfoList);
//        String str = "             id|text|1|主键id\n" +
//                "                  field_id|text|0|场次id\n" +
//                "                  field_name|text|0|场次名称\n" +
//                "                  unit_type_id|text|0|猪舍类型id\n" +
//                "                  unit_type_name|text|0|猪舍类型名称\n" +
//                "                  batch_id|text|0|批次号\n" +
//                "                  batch_name|text|0|批次名称\n" +
//                "                  put_segment_id|text|0|装猪类型id\n" +
//                "                  put_segment_name|text|0|装猪类型名称\n" +
//                "                  unit_id|text|0|单元id\n" +
//                "                  unit_no|bigint|0|单元号\n" +
//                "                   avg_age|bigint|0|日龄\n" +
//                "                   stock_amount|bigint|0|存栏头数\n" +
//                "                   weaning_standard_amount|bigint|0|断奶标准头数\n" +
//                "                   total_milk|bigint|0|日用奶水总量(ml)\n" +
//                "                   avg_milk|bigint|0|日头均奶水量(ml)\n" +
//                "                   avg_feed_intake|bigint|0|日头均采食量(ml)\n" +
//                "                   is_qualified|bigint|0|是否合格 0-否 1-是\n" +
//                "                   continue_supply_milk_days|bigint|0|连续供奶天数\n" +
//                "                   seven_day_matter|bigint|0|近7天干物质\n" +
//                "                   total_dry_milk|bigint|0|用奶粉量\n" +
//                "                   avg_milk_cost|decimal|0|头均奶粉成本(元)\n" +
//                "                   survival_amount|bigint|0|成活头数\n" +
//                "                   effective_date|ts|0|有效日期\n" +
//                "                   dt|text|0|分区";
//        List<String> colStrList = Arrays.stream(str.split("\n")).map(String::trim).toList();
//        genHoloCreateTableSql("feed_periodic.my_add_milk_unit_day_score_ptn", "乳猪补奶单元日度成绩表", colStrList);
    }


    /**
     * 根据表别名、拼接字段，生成拼接更新sql
     * @param tableName     目的表名
     * @param tableAlias    表别名
     * @param columnList    拼接字段集合
     * @date 2025/09/23 16:54
     **/
    public static void genConcatUpdateSql(String tableName, String tableAlias, List<UpdateColumnInfo> columnList) {
        if (CollectionUtil.isEmpty(columnList)) {
            return;
        }
        UpdateColumnInfo pkColumn = columnList.stream().filter(d -> Objects.equals(true, d.getIsPk())).findAny().orElse(null);
        if (Objects.isNull(pkColumn)) {
            throw new IllegalArgumentException("未传递主键列信息");
        }
        columnList.remove(pkColumn);
        //是否有别名
        boolean haveAlias = StringUtil.isNotEmpty(tableAlias);
        //剔除主键列
        String colStr = columnList.stream().map(d -> d.getIsNeedQuote() ? String.format("'%s =*',%s,'*,'", d.getDestColumnName(), haveAlias ? String.format("%s.%s", tableAlias, d.getSourceColumnName()) : d.getSourceColumnName()) : String.format("'%s =',%s,','", d.getDestColumnName(), haveAlias ? String.format("%s.%s", tableAlias, d.getSourceColumnName()) : d.getSourceColumnName())).collect(Collectors.joining(","));
        //去掉最后一个,
        String colSql = colStr.endsWith("'*,'") ? colStr.substring(0, colStr.lastIndexOf("'*,'")) + "'*'," : colStr.substring(0, colStr.lastIndexOf("','"));
        String updSql = String.format("'update %s set ',%s ' where %s =", tableName, colSql, pkColumn.getDestColumnName());
        //拼接主键值
        String pkVal = haveAlias ? String.format("%s.%s", tableAlias, pkColumn.getSourceColumnName()) : pkColumn.getSourceColumnName();
        String whereSql = pkColumn.getIsNeedQuote() ? String.format("*',%s,'*;'", pkVal) : String.format("',%s,';'", pkVal);
        String resSql = String.format("select concat(%s%s) as sql_str from t;", updSql, whereSql);
        System.err.println(resSql);
    }

    /**
     * 根据表名、列信息生成建表语句
     * @param tableName 表名
     *  @param tableComment 表注释
     * @param columnStrList 列信息集合-->列名|类型|长度,默认值|是否主键|注释:     name|varchar|32,''|0|姓名
     *                      列信息集合-->列名|类型|长度|是否主键,自增|注释:         id|int|11|1,1|主键id
     * @date 2025/09/23 16:52
     **/
    public static String genMysqlCreateTableSql(String tableName, String tableComment, List<String> columnStrList) {
        if (CollectionUtil.isEmpty(columnStrList)) {
            return null;
        }
        //主键列
        AtomicReference<String> pkCol = new AtomicReference<>();
        //初始化列信息
        List<ColumnInfo> columnInfoList = columnStrList.stream().map(SqlUtil::initColumnInfo).collect(Collectors.toList());
        //组装sql
        String columStr = columnInfoList.stream().map(d -> assembleColSql(pkCol, d)).filter(StringUtil::isNotEmpty).collect(Collectors.joining(",\n\t"));
        //主键，添加PRIMARY KEY
        if (StringUtil.isNotEmpty(pkCol.get())) {
            columStr = String.format("%s,\n\tPRIMARY KEY (`%s`)", columStr, PoJoUtil.humpToLine(pkCol.get()));
        }
        return String.format("CREATE TABLE IF NOT EXISTS `%s` (\n\t%s\n)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='%s';", PoJoUtil.humpToLine(tableName), columStr, tableComment);
    }


    /**
     * holo建表
     * @param tableName    表名(模式.表名)
     * @param tableComment    表注释
     * @param columnStrList    列集合(列名|类型|是否主键(0|1)|注释)
     * @date 2025/10/03 11:28
     **/
    public static String genHoloCreateTableSql(String tableName, String tableComment, List<String> columnStrList) {
        if (CollectionUtil.isEmpty(columnStrList)) {
            return null;
        }
        List<String> colDfSql = Lists.newArrayList();
        List<ColumnInfo> columnInfoList = columnStrList.stream().map(c -> {
            String[] cols = c.split("\\|");
            if (cols.length != 4) {
                throw new IllegalArgumentException("数据格式不合法，正确格式：列名|数据类型|长度|注释");
            }
            String columnName = PoJoUtil.humpToLine(cols[0]);
            ColumnInfo columnInfo = new ColumnInfo();
            columnInfo.setColumnName(columnName).setColumnType(cols[1]).setIsPk("1".equals(cols[2])).setColumnComment(cols[3]);
            if (columnInfo.getIsPk()) {//主键
                colDfSql.add(String.format("\t%s text NOT NULL", columnName));
            } else {
                if ("ts".equalsIgnoreCase(columnInfo.getColumnType())) {//时间戳
                    colDfSql.add(String.format("\t%s timestamp with time zone", columnName));
                } else {
                    colDfSql.add(String.format("\t%s %s", columnName, columnInfo.getColumnType()));
                }
            }
            return columnInfo;
        }).collect(Collectors.toList());
        ColumnInfo pkInfo = columnInfoList.stream().filter(ColumnInfo::getIsPk).findAny().orElse(null);
        if (Objects.isNull(pkInfo)) {
            throw new IllegalArgumentException("表必须有主键!");
        }
        //添加PRIMARY KEY (id)
        colDfSql.add(String.format("\tPRIMARY KEY (%s)", pkInfo.getColumnName()));
        String crtSql = String.format("CREATE TABLE IF NOT EXISTS %s (\n%s\n);\n", tableName, String.join(",\n", colDfSql));
        //输出注释列注释
        String commentStr = columnInfoList.stream().map(d -> String.format("COMMENT ON COLUMN %s.%s IS '%s';", tableName, d.getColumnName(), d.getColumnComment())).collect(Collectors.joining("\n"));
        //createStr+表注释+列注释
        return crtSql + String.format("COMMENT ON TABLE %s IS '%s';\n", tableName, tableComment) + commentStr;
    }


    /**
     * 组装列sql
     * @param pkCol 主键列
     * @param columnInfo 列信息
     * @date 2025/09/25 14:55
     **/
    private static String assembleColSql(AtomicReference<String> pkCol, ColumnInfo columnInfo) {
        if (columnInfo.getIsPk()) {
            pkCol.set(columnInfo.getColumnName());
            //自增
            if (columnInfo.getAutoIncrement()) {
                return String.format("`%s` %s(%s) NOT NULL AUTO_INCREMENT COMMENT '%s'", columnInfo.getColumnName(), columnInfo.getColumnType(), columnInfo.getLength(), columnInfo.getColumnComment());
            } else {
                return String.format("`%s` %s(%s) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '%s'", columnInfo.getColumnName(), columnInfo.getColumnType(), columnInfo.getLength(), columnInfo.getColumnComment());
            }
        }
        if ("crt_time".equalsIgnoreCase(columnInfo.getColumnName())) {
            return String.format("`%s` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '%s'", columnInfo.getColumnName(), columnInfo.getColumnComment());
        } else if ("upd_time".equalsIgnoreCase(columnInfo.getColumnName())) {
            return String.format("`%s` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '%s'", columnInfo.getColumnName(), columnInfo.getColumnComment());
        }
        String columnType = columnInfo.getColumnType().toLowerCase();
        String resSqlStr = null;
        switch (columnType) {
            case "int":
            case "bigint":
            case "tinyint":
                resSqlStr = String.format("`%s` %s(%s) DEFAULT %s COMMENT '%s'", columnInfo.getColumnName(), columnInfo.getColumnType(), columnInfo.getLength(), handle(columnInfo.getDefaultValue()), columnInfo.getColumnComment());
                break;
            case "varchar":
                resSqlStr = String.format("`%s` %s(%s) DEFAULT %s COLLATE utf8mb4_unicode_ci COMMENT '%s'", columnInfo.getColumnName(), columnInfo.getColumnType(), columnInfo.getLength(), handle(columnInfo.getDefaultValue()), columnInfo.getColumnComment());
                break;
            case "date":
            case "datetime":
            case "timestamp":
                resSqlStr = String.format("`%s` %s DEFAULT NULL COMMENT '%s'", columnInfo.getColumnName(), columnInfo.getColumnType(), columnInfo.getColumnComment());
                break;
        }
        return resSqlStr;
    }


    /**
     * 初始化列信息
     *列信息集合-->列名|类型|长度,默认值|是否主键|注释:     name|varchar|32,''|0|姓名
     *列信息集合-->列名|类型|长度|是否主键,自增|注释:        id|int|11|1,1|主键id
     * @param columnStr 列字符串
     * @date 2025/09/24 09:08
     **/
    private static ColumnInfo initColumnInfo(String columnStr) {
        if (StringUtil.isEmpty(columnStr)) {
            throw new IllegalArgumentException("列信息不能为空!");
        }
        String[] cols = columnStr.split("\\|");
        int length = cols.length;
        ColumnInfo columnInfo = new ColumnInfo();
        if (length != 5) {
            throw new IllegalArgumentException("列信息格式不合法,使用方式:\n列名|类型|长度,默认值|是否主键|注释\n列名|类型|长度|是否主键,自增|注释!");
        }
        //列名处理，驼峰转下划线
        columnInfo.setColumnName(PoJoUtil.humpToLine(cols[0])).setColumnType(cols[1]).setColumnComment(cols[4]);
        //列长度信息(长度,默认值)
        String colLenStr = cols[2];
        if (colLenStr.contains(",")) {
            columnInfo.setLength(Integer.parseInt(colLenStr.split(",")[0])).setDefaultValue(colLenStr.split(",")[1]);
        } else {
            columnInfo.setLength(Integer.parseInt(colLenStr));
        }
        //是否主键信息(是否主键,自增)
        String pkStr = cols[3];
        if (pkStr.contains(",")) {
            columnInfo.setIsPk("1".equals(pkStr.split(",")[0])).setAutoIncrement("1".equals(pkStr.split(",")[1]));
        } else {
            columnInfo.setIsPk("1".equals(pkStr));
        }
        return columnInfo;
    }


    /**
     * 列集合，统一类型处理
     * @param columnStrList    列集合 '列名|注释,列名|注释'
     * @param columnType    类型
     * @param length    长度
     * @param defaultValue    莫默认值
     * @date 2025/09/23 18:42
     **/
    public static List<String> initCommonType(List<String> columnStrList, String columnType, int length, String defaultValue) {
        if (CollectionUtil.isEmpty(columnStrList)) {
            return Lists.newArrayList();
        }
        return columnStrList.stream().map(d -> String.format("%s|%s|%s|%s|%s", PoJoUtil.humpToLine(d.split("\\|")[0]), columnType, length, defaultValue, d.split("\\|")[1])).collect(Collectors.toList());
    }


    private static String handle(String defaultValue) {
        if ("''".equals(defaultValue)) {
            return defaultValue;
        }
        return "null".equalsIgnoreCase(defaultValue) ? "NULL" : String.format("'%s'", defaultValue);
    }


    /**
     * 列信息
     * @author guos
     * @date 2025/09/23 16:52
     **/
    @Data
    @FieldNameConstants
    @Accessors(chain = true)
    public static class ColumnInfo {

        /**
         * 列名
         */
        private String columnName;

        /**
         * 列名
         */
        private String columnType;

        /**
         * 长度
         */
        private Integer length;

        /**
         * 是否主键
         */
        private Boolean isPk = false;

        /**
         * 是否自增
         */
        private Boolean autoIncrement = false;

        /**
         * 注释
         */
        private String columnComment;

        /**
         * 默认值
         */
        private String defaultValue;
    }


    /**
     * 更新列信息
     * @author guos
     * @date 2025/09/23 16:52
     **/
    @Data
    @FieldNameConstants
    @Accessors(chain = true)
    @AllArgsConstructor
    @NoArgsConstructor
    public static class UpdateColumnInfo {

        /**
         * 目的列名
         */
        private String destColumnName;

        /**
         * 是否主键
         */
        private Boolean isPk;

        /**
         * 源列名
         */
        private String sourceColumnName;

        /**
         * 是否需要引号
         */
        private Boolean isNeedQuote = false;
    }
}
